from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from config import SQLALCHEMY_DATABASE_URI, SQLALCHEMY_ECHO
import config
"""
数据库链接配置
"""

def getSession():
    engine = create_engine(config.SQLALCHEMY_DATABASE_URI,
                           echo=config.SQLALCHEMY_ECHO,
                           pool_size=10,
                           pool_recycle=7200,
                           isolation_level='READ_COMMITTED',
                           pool_pre_ping=True,
                           encoding='utf-8')
    """
    engine = engine_from_config('../config.py')
    """
    DBSessionFactory = sessionmaker(bind=engine)
    session = DBSessionFactory()
    return session

def db_query(sql,kwargs):
    """
    原生sql查询列表
    """
    session = getSession()
    return session.execute(text(sql),kwargs).fetchall()


def db_execute(sql, settings=None, echo=None):
    """
    原生sql查询总数
    """
    if settings is None:
        settings = SQLALCHEMY_DATABASE_URI

    if echo is None:
        echo = SQLALCHEMY_ECHO

    return create_engine(settings, echo=echo).connect().execute(text(sql)).rowcount

"""
以下shi测试  关联查询
sql = "SELECT " \
      "u.`user_name`,r.`name` " \
      "FROM `user` " \
      "u ,role r ,user_role ur " \
      "WHERE " \
      "u.`id`=ur.`user_id` AND ur.`role_id` = r.`id` " \
      " AND u.user_name =:userName "
param = {}
param['userName'] = 'liaokailei'
print(param)
listObj = db_query(sql,param)
print(listObj)
"""